'''
db_interfacer.py

This module handles querying the grad.db database for
course information.
'''
try:
    import sqlite3
except ImportError:
    print 'Cannot load sqlite3'

'''
filter(dept, gep)

Parameters: dept - a string for the department name
            gep - a string for the gep type

Returns: A list of tuples with id, title, department, credits,
         description, tuple of prerequsites, tuple of fulfilled minors,
         and a tuple of fulfilled geps
'''
def filter(dept, gep):

    # Generate a list of filtered courses
    courses = []
    try:
        # Get a connection to the db and setup a cursor object.
        conn = sqlite3.connect('grad.db')
        conn.text_factory = str
        c = conn.cursor()

        # query the database
        if(dept == 'All' and gep == 'All'):
            c.execute("select id, title, dept, credits, description from " + 
            "course")
        elif(dept == 'All' and not(gep == 'All')):
            c.execute("select id, title, dept, credits, description from " +
            "(select * from course) natural join (select * from gep " + 
            "where type = ?)", (gep, ))
        elif(not(dept == 'All') and gep == 'All'):
            c.execute("select id, title, dept, credits, description from " + 
            "course where dept = ?", (dept, ))
        else:
            c.execute("select id, title, dept, credits, description from " + 
            "(select * from course where dept = ?) natural join (select * " + 
            "from gep where type = ?)", (dept, gep))

        # get cursors to add prereqs, geps, and minors as we go
        preq_cur = conn.cursor()
        gep_cur = conn.cursor()
        min_cur = conn.cursor()

        for row in c:
            # create some containers
            preqs = ()
            geps = ()
            mins = ()

            # Add a tuple of all prereqs
            preq_cur.execute("select prereq_id from prereq " + 
                             "where id = ?", (row[0], ))
            for p in preq_cur:
                # now create a tuple for "OR" Prerequisites
                ors = p[0]
                ors = ors.split('||')
                fin_preqs = ()
                for sub in ors:
                    fin_preqs = fin_preqs + (sub,)

                # if we had "OR", add them, else add the single course
                if(len(fin_preqs) > 1):
                    preqs = preqs + (fin_preqs,)
                else:
                    preqs = preqs + fin_preqs
            if(len(preqs) == 0):
                preqs = ('', )
            row = row + (preqs, )

            # Add a tuple of all geps fulfilled
            gep_cur.execute('select type from gep where id = ?', (row[0], ))
            for g in gep_cur:
                geps = geps + g
            if(len(geps) == 0):
                geps = ('', )
            row = row + (geps, )

            # Add a tuple of all minors fulfilled
            min_cur.execute('select dept from minor where id = ?', (row[0], ))
            for m in min_cur:
                mins = mins + m
            if(len(mins) == 0):
                mins = ('', )
            row = row + (mins, )

            # Add the course to the course list
            courses.append(row)

    except sqlite3.OperationalError:
        conn.close()
        return ['']


    return courses
